{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 1.\t读取药品销售清单进行分析.\n",
    "\n",
    "#### 要求：\n",
    "#### （1）pandas模块读取“药品销售清单.xlsx”。显示购买金额最高的前20名顾客的编码以及购买总额信息。\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>购买时间</th>\n",
       "      <th>顾客</th>\n",
       "      <th>药店编码</th>\n",
       "      <th>药品编码</th>\n",
       "      <th>数量</th>\n",
       "      <th>单价</th>\n",
       "      <th>总价</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-01-01 08:56:00</td>\n",
       "      <td>BID1062</td>\n",
       "      <td>SID13</td>\n",
       "      <td>MID595</td>\n",
       "      <td>3</td>\n",
       "      <td>15.9</td>\n",
       "      <td>47.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019-01-01 08:56:00</td>\n",
       "      <td>BID1062</td>\n",
       "      <td>SID13</td>\n",
       "      <td>MID420</td>\n",
       "      <td>1</td>\n",
       "      <td>45.0</td>\n",
       "      <td>45.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-01-01 09:07:01</td>\n",
       "      <td>BID3241</td>\n",
       "      <td>SID15</td>\n",
       "      <td>MID468</td>\n",
       "      <td>2</td>\n",
       "      <td>20.1</td>\n",
       "      <td>40.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-01-01 09:07:01</td>\n",
       "      <td>BID3241</td>\n",
       "      <td>SID15</td>\n",
       "      <td>MID501</td>\n",
       "      <td>4</td>\n",
       "      <td>31.8</td>\n",
       "      <td>127.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019-01-01 09:13:27</td>\n",
       "      <td>BID1899</td>\n",
       "      <td>SID14</td>\n",
       "      <td>MID465</td>\n",
       "      <td>4</td>\n",
       "      <td>54.4</td>\n",
       "      <td>217.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99728</th>\n",
       "      <td>2019-10-17 11:43:13</td>\n",
       "      <td>BID3265</td>\n",
       "      <td>SID19</td>\n",
       "      <td>MID272</td>\n",
       "      <td>2</td>\n",
       "      <td>75.5</td>\n",
       "      <td>151.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99729</th>\n",
       "      <td>2019-10-17 11:45:23</td>\n",
       "      <td>BID0704</td>\n",
       "      <td>SID01</td>\n",
       "      <td>MID337</td>\n",
       "      <td>2</td>\n",
       "      <td>38.2</td>\n",
       "      <td>76.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99730</th>\n",
       "      <td>2019-10-17 11:51:09</td>\n",
       "      <td>BID0222</td>\n",
       "      <td>SID05</td>\n",
       "      <td>MID328</td>\n",
       "      <td>2</td>\n",
       "      <td>57.5</td>\n",
       "      <td>115.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99731</th>\n",
       "      <td>2019-10-17 11:51:09</td>\n",
       "      <td>BID0222</td>\n",
       "      <td>SID05</td>\n",
       "      <td>MID620</td>\n",
       "      <td>1</td>\n",
       "      <td>91.4</td>\n",
       "      <td>91.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99732</th>\n",
       "      <td>2019-10-17 11:51:09</td>\n",
       "      <td>BID0222</td>\n",
       "      <td>SID05</td>\n",
       "      <td>MID441</td>\n",
       "      <td>2</td>\n",
       "      <td>71.6</td>\n",
       "      <td>143.2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>99733 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                     购买时间       顾客   药店编码    药品编码  数量    单价     总价\n",
       "0     2019-01-01 08:56:00  BID1062  SID13  MID595   3  15.9   47.7\n",
       "1     2019-01-01 08:56:00  BID1062  SID13  MID420   1  45.0   45.0\n",
       "2     2019-01-01 09:07:01  BID3241  SID15  MID468   2  20.1   40.2\n",
       "3     2019-01-01 09:07:01  BID3241  SID15  MID501   4  31.8  127.2\n",
       "4     2019-01-01 09:13:27  BID1899  SID14  MID465   4  54.4  217.6\n",
       "...                   ...      ...    ...     ...  ..   ...    ...\n",
       "99728 2019-10-17 11:43:13  BID3265  SID19  MID272   2  75.5  151.0\n",
       "99729 2019-10-17 11:45:23  BID0704  SID01  MID337   2  38.2   76.4\n",
       "99730 2019-10-17 11:51:09  BID0222  SID05  MID328   2  57.5  115.0\n",
       "99731 2019-10-17 11:51:09  BID0222  SID05  MID620   1  91.4   91.4\n",
       "99732 2019-10-17 11:51:09  BID0222  SID05  MID441   2  71.6  143.2\n",
       "\n",
       "[99733 rows x 7 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "df_药品销售清单 = pd.read_excel(\"药品销售清单.xlsx\")\n",
    "df_药品销售清单"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 计算购药总价"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>总价</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>顾客</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>BID0001</th>\n",
       "      <td>3052.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BID0002</th>\n",
       "      <td>1885.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BID0003</th>\n",
       "      <td>3058.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BID0004</th>\n",
       "      <td>3859.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BID0005</th>\n",
       "      <td>2629.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BID3717</th>\n",
       "      <td>3797.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BID3718</th>\n",
       "      <td>1958.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BID3719</th>\n",
       "      <td>3897.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BID3720</th>\n",
       "      <td>2166.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BID3721</th>\n",
       "      <td>2392.2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3721 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             总价\n",
       "顾客             \n",
       "BID0001  3052.1\n",
       "BID0002  1885.9\n",
       "BID0003  3058.5\n",
       "BID0004  3859.2\n",
       "BID0005  2629.4\n",
       "...         ...\n",
       "BID3717  3797.4\n",
       "BID3718  1958.7\n",
       "BID3719  3897.4\n",
       "BID3720  2166.7\n",
       "BID3721  2392.2\n",
       "\n",
       "[3721 rows x 1 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfg_顾客=df_药品销售清单[['顾客','总价']].groupby(by = '顾客')\n",
    "df_顾客购药总额=dfg_顾客.sum()\n",
    "df_顾客购药总额\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 按购药总价降序排序"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>总价</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>顾客</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>BID2350</th>\n",
       "      <td>9798.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BID2391</th>\n",
       "      <td>8949.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BID0596</th>\n",
       "      <td>8304.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BID2652</th>\n",
       "      <td>6429.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BID2426</th>\n",
       "      <td>6386.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BID2551</th>\n",
       "      <td>203.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BID1158</th>\n",
       "      <td>187.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BID2210</th>\n",
       "      <td>176.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BID2508</th>\n",
       "      <td>172.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BID3519</th>\n",
       "      <td>145.8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3721 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             总价\n",
       "顾客             \n",
       "BID2350  9798.7\n",
       "BID2391  8949.5\n",
       "BID0596  8304.8\n",
       "BID2652  6429.9\n",
       "BID2426  6386.5\n",
       "...         ...\n",
       "BID2551   203.6\n",
       "BID1158   187.3\n",
       "BID2210   176.8\n",
       "BID2508   172.2\n",
       "BID3519   145.8\n",
       "\n",
       "[3721 rows x 1 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_顾客购药总额=df_顾客购药总额.sort_values(by='总价',axis=0,ascending=False)\n",
    "df_顾客购药总额"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "####  显示总额前20名顾客编码以及总额"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "顾客\n",
       "BID2350    9798.7\n",
       "BID2391    8949.5\n",
       "BID0596    8304.8\n",
       "BID2652    6429.9\n",
       "BID2426    6386.5\n",
       "BID1131    6363.0\n",
       "BID0741    6278.7\n",
       "BID2603    6261.9\n",
       "BID2118    6157.3\n",
       "BID0788    5910.2\n",
       "BID3313    5903.1\n",
       "BID0462    5888.3\n",
       "BID3425    5880.1\n",
       "BID3413    5868.0\n",
       "BID1994    5858.6\n",
       "BID1006    5849.5\n",
       "BID0666    5782.0\n",
       "BID2916    5766.7\n",
       "BID1178    5739.0\n",
       "BID1437    5726.0\n",
       "Name: 总价, dtype: float64"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sr_总价前20名顾客=df_顾客购药总额.head(20)['总价']\n",
    "sr_总价前20名顾客\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### （2）筛选购药总价最高的20个人的完整购药记录"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0        BID1062\n",
       "1        BID1062\n",
       "2        BID3241\n",
       "3        BID3241\n",
       "4        BID1899\n",
       "          ...   \n",
       "99728    BID3265\n",
       "99729    BID0704\n",
       "99730    BID0222\n",
       "99731    BID0222\n",
       "99732    BID0222\n",
       "Name: 顾客, Length: 99733, dtype: object"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_药品销售清单['顾客']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "99733"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lst_sel=[bid in sr_总价前20名顾客  for bid in df_药品销售清单['顾客'] ]\n",
    "len(lst_sel)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>购买时间</th>\n",
       "      <th>顾客</th>\n",
       "      <th>药店编码</th>\n",
       "      <th>药品编码</th>\n",
       "      <th>数量</th>\n",
       "      <th>单价</th>\n",
       "      <th>总价</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2019-01-01 09:28:37</td>\n",
       "      <td>BID2916</td>\n",
       "      <td>SID18</td>\n",
       "      <td>MID477</td>\n",
       "      <td>1</td>\n",
       "      <td>9.3</td>\n",
       "      <td>9.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2019-01-01 09:28:37</td>\n",
       "      <td>BID2916</td>\n",
       "      <td>SID18</td>\n",
       "      <td>MID472</td>\n",
       "      <td>2</td>\n",
       "      <td>36.3</td>\n",
       "      <td>72.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2019-01-01 09:28:37</td>\n",
       "      <td>BID2916</td>\n",
       "      <td>SID18</td>\n",
       "      <td>MID498</td>\n",
       "      <td>2</td>\n",
       "      <td>88.7</td>\n",
       "      <td>177.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54</th>\n",
       "      <td>2019-01-01 11:18:38</td>\n",
       "      <td>BID0596</td>\n",
       "      <td>SID16</td>\n",
       "      <td>MID393</td>\n",
       "      <td>2</td>\n",
       "      <td>35.0</td>\n",
       "      <td>70.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>2019-01-01 11:18:38</td>\n",
       "      <td>BID0596</td>\n",
       "      <td>SID16</td>\n",
       "      <td>MID296</td>\n",
       "      <td>4</td>\n",
       "      <td>64.3</td>\n",
       "      <td>257.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98767</th>\n",
       "      <td>2019-10-14 15:17:29</td>\n",
       "      <td>BID2652</td>\n",
       "      <td>SID03</td>\n",
       "      <td>MID599</td>\n",
       "      <td>2</td>\n",
       "      <td>60.0</td>\n",
       "      <td>120.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98768</th>\n",
       "      <td>2019-10-14 15:17:29</td>\n",
       "      <td>BID2652</td>\n",
       "      <td>SID03</td>\n",
       "      <td>MID258</td>\n",
       "      <td>2</td>\n",
       "      <td>13.6</td>\n",
       "      <td>27.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98769</th>\n",
       "      <td>2019-10-14 15:17:29</td>\n",
       "      <td>BID2652</td>\n",
       "      <td>SID03</td>\n",
       "      <td>MID507</td>\n",
       "      <td>2</td>\n",
       "      <td>19.7</td>\n",
       "      <td>39.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98770</th>\n",
       "      <td>2019-10-14 15:17:29</td>\n",
       "      <td>BID2652</td>\n",
       "      <td>SID03</td>\n",
       "      <td>MID378</td>\n",
       "      <td>2</td>\n",
       "      <td>67.5</td>\n",
       "      <td>135.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98792</th>\n",
       "      <td>2019-10-14 16:06:23</td>\n",
       "      <td>BID1178</td>\n",
       "      <td>SID02</td>\n",
       "      <td>MID465</td>\n",
       "      <td>2</td>\n",
       "      <td>54.4</td>\n",
       "      <td>108.8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1203 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                     购买时间       顾客   药店编码    药品编码  数量    单价     总价\n",
       "11    2019-01-01 09:28:37  BID2916  SID18  MID477   1   9.3    9.3\n",
       "12    2019-01-01 09:28:37  BID2916  SID18  MID472   2  36.3   72.6\n",
       "13    2019-01-01 09:28:37  BID2916  SID18  MID498   2  88.7  177.4\n",
       "54    2019-01-01 11:18:38  BID0596  SID16  MID393   2  35.0   70.0\n",
       "55    2019-01-01 11:18:38  BID0596  SID16  MID296   4  64.3  257.2\n",
       "...                   ...      ...    ...     ...  ..   ...    ...\n",
       "98767 2019-10-14 15:17:29  BID2652  SID03  MID599   2  60.0  120.0\n",
       "98768 2019-10-14 15:17:29  BID2652  SID03  MID258   2  13.6   27.2\n",
       "98769 2019-10-14 15:17:29  BID2652  SID03  MID507   2  19.7   39.4\n",
       "98770 2019-10-14 15:17:29  BID2652  SID03  MID378   2  67.5  135.0\n",
       "98792 2019-10-14 16:06:23  BID1178  SID02  MID465   2  54.4  108.8\n",
       "\n",
       "[1203 rows x 7 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lst_sel=[bid in sr_总价前20名顾客  for bid in df_药品销售清单['顾客'] ] \n",
    "df_总额前20名顾客药品销售清单=df_药品销售清单.loc[lst_sel]\n",
    "df_总额前20名顾客药品销售清单"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.\t获取购买金额最高的前20名顾客的全部购买记录，用透视表分析：\n",
    "\n",
    "### （1）使用pivot_table函数制作透视表，分析每名顾客每一天购买的药品的总金额。 \n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\zhengyang\\AppData\\Local\\Temp\\ipykernel_34152\\850920430.py:8: FutureWarning: The behavior of DataFrame concatenation with empty or all-NA entries is deprecated. In a future version, this will no longer exclude empty or all-NA columns when determining the result dtypes. To retain the old behavior, exclude the relevant entries before the concat operation.\n",
      "  df_总额前20名顾客药品销售清单.loc['购买时间']=sr_购买日期\n",
      "C:\\Users\\zhengyang\\AppData\\Local\\Temp\\ipykernel_34152\\850920430.py:8: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_总额前20名顾客药品销售清单.loc['购买时间']=sr_购买日期\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>购买时间</th>\n",
       "      <th>顾客</th>\n",
       "      <th>药店编码</th>\n",
       "      <th>药品编码</th>\n",
       "      <th>数量</th>\n",
       "      <th>单价</th>\n",
       "      <th>总价</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2019-01-01 09:28:37</td>\n",
       "      <td>BID2916</td>\n",
       "      <td>SID18</td>\n",
       "      <td>MID477</td>\n",
       "      <td>1.0</td>\n",
       "      <td>9.3</td>\n",
       "      <td>9.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2019-01-01 09:28:37</td>\n",
       "      <td>BID2916</td>\n",
       "      <td>SID18</td>\n",
       "      <td>MID472</td>\n",
       "      <td>2.0</td>\n",
       "      <td>36.3</td>\n",
       "      <td>72.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2019-01-01 09:28:37</td>\n",
       "      <td>BID2916</td>\n",
       "      <td>SID18</td>\n",
       "      <td>MID498</td>\n",
       "      <td>2.0</td>\n",
       "      <td>88.7</td>\n",
       "      <td>177.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54</th>\n",
       "      <td>2019-01-01 11:18:38</td>\n",
       "      <td>BID0596</td>\n",
       "      <td>SID16</td>\n",
       "      <td>MID393</td>\n",
       "      <td>2.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>70.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>2019-01-01 11:18:38</td>\n",
       "      <td>BID0596</td>\n",
       "      <td>SID16</td>\n",
       "      <td>MID296</td>\n",
       "      <td>4.0</td>\n",
       "      <td>64.3</td>\n",
       "      <td>257.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98768</th>\n",
       "      <td>2019-10-14 15:17:29</td>\n",
       "      <td>BID2652</td>\n",
       "      <td>SID03</td>\n",
       "      <td>MID258</td>\n",
       "      <td>2.0</td>\n",
       "      <td>13.6</td>\n",
       "      <td>27.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98769</th>\n",
       "      <td>2019-10-14 15:17:29</td>\n",
       "      <td>BID2652</td>\n",
       "      <td>SID03</td>\n",
       "      <td>MID507</td>\n",
       "      <td>2.0</td>\n",
       "      <td>19.7</td>\n",
       "      <td>39.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98770</th>\n",
       "      <td>2019-10-14 15:17:29</td>\n",
       "      <td>BID2652</td>\n",
       "      <td>SID03</td>\n",
       "      <td>MID378</td>\n",
       "      <td>2.0</td>\n",
       "      <td>67.5</td>\n",
       "      <td>135.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98792</th>\n",
       "      <td>2019-10-14 16:06:23</td>\n",
       "      <td>BID1178</td>\n",
       "      <td>SID02</td>\n",
       "      <td>MID465</td>\n",
       "      <td>2.0</td>\n",
       "      <td>54.4</td>\n",
       "      <td>108.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>购买时间</th>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1204 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                     购买时间       顾客   药店编码    药品编码   数量    单价     总价\n",
       "11    2019-01-01 09:28:37  BID2916  SID18  MID477  1.0   9.3    9.3\n",
       "12    2019-01-01 09:28:37  BID2916  SID18  MID472  2.0  36.3   72.6\n",
       "13    2019-01-01 09:28:37  BID2916  SID18  MID498  2.0  88.7  177.4\n",
       "54    2019-01-01 11:18:38  BID0596  SID16  MID393  2.0  35.0   70.0\n",
       "55    2019-01-01 11:18:38  BID0596  SID16  MID296  4.0  64.3  257.2\n",
       "...                   ...      ...    ...     ...  ...   ...    ...\n",
       "98768 2019-10-14 15:17:29  BID2652  SID03  MID258  2.0  13.6   27.2\n",
       "98769 2019-10-14 15:17:29  BID2652  SID03  MID507  2.0  19.7   39.4\n",
       "98770 2019-10-14 15:17:29  BID2652  SID03  MID378  2.0  67.5  135.0\n",
       "98792 2019-10-14 16:06:23  BID1178  SID02  MID465  2.0  54.4  108.8\n",
       "购买时间                  NaT      NaN    NaN     NaN  NaN   NaN    NaN\n",
       "\n",
       "[1204 rows x 7 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "sr_购买时间=pd.to_datetime(df_总额前20名顾客药品销售清单['购买时间'])\n",
    "\n",
    "sr_购买日期=pd.Series([ts_i.date() for ts_i in sr_购买时间],\n",
    "                  index=df_总额前20名顾客药品销售清单.index )\n",
    "\n",
    "df_总额前20名顾客药品销售清单.loc['购买时间']=sr_购买日期\n",
    "df_总额前20名顾客药品销售清单"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>顾客</th>\n",
       "      <th>BID0462</th>\n",
       "      <th>BID0596</th>\n",
       "      <th>BID0666</th>\n",
       "      <th>BID0741</th>\n",
       "      <th>BID0788</th>\n",
       "      <th>BID1006</th>\n",
       "      <th>BID1131</th>\n",
       "      <th>BID1178</th>\n",
       "      <th>BID1437</th>\n",
       "      <th>BID1994</th>\n",
       "      <th>BID2118</th>\n",
       "      <th>BID2350</th>\n",
       "      <th>BID2391</th>\n",
       "      <th>BID2426</th>\n",
       "      <th>BID2603</th>\n",
       "      <th>BID2652</th>\n",
       "      <th>BID2916</th>\n",
       "      <th>BID3313</th>\n",
       "      <th>BID3413</th>\n",
       "      <th>BID3425</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>购买时间</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2019-01-01 09:28:37</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>259.3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-01 11:18:38</th>\n",
       "      <td>NaN</td>\n",
       "      <td>1369.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-01 14:14:41</th>\n",
       "      <td>180.6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-02 14:17:14</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>214.8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-02 16:34:44</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>323.8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-10-12 11:02:22</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>249.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-10-13 16:35:29</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>148.7</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-10-13 19:09:28</th>\n",
       "      <td>324.6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-10-14 15:17:29</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>427.2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-10-14 16:06:23</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>108.8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>301 rows × 20 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "顾客                   BID0462  BID0596  BID0666  BID0741  BID0788  BID1006  \\\n",
       "购买时间                                                                        \n",
       "2019-01-01 09:28:37      NaN      NaN      NaN      NaN      NaN      NaN   \n",
       "2019-01-01 11:18:38      NaN   1369.5      NaN      NaN      NaN      NaN   \n",
       "2019-01-01 14:14:41    180.6      NaN      NaN      NaN      NaN      NaN   \n",
       "2019-01-02 14:17:14      NaN      NaN      NaN      NaN      NaN      NaN   \n",
       "2019-01-02 16:34:44      NaN      NaN      NaN      NaN      NaN      NaN   \n",
       "...                      ...      ...      ...      ...      ...      ...   \n",
       "2019-10-12 11:02:22      NaN      NaN      NaN      NaN      NaN      NaN   \n",
       "2019-10-13 16:35:29      NaN      NaN      NaN      NaN      NaN      NaN   \n",
       "2019-10-13 19:09:28    324.6      NaN      NaN      NaN      NaN      NaN   \n",
       "2019-10-14 15:17:29      NaN      NaN      NaN      NaN      NaN      NaN   \n",
       "2019-10-14 16:06:23      NaN      NaN      NaN      NaN      NaN      NaN   \n",
       "\n",
       "顾客                   BID1131  BID1178  BID1437  BID1994  BID2118  BID2350  \\\n",
       "购买时间                                                                        \n",
       "2019-01-01 09:28:37      NaN      NaN      NaN      NaN      NaN      NaN   \n",
       "2019-01-01 11:18:38      NaN      NaN      NaN      NaN      NaN      NaN   \n",
       "2019-01-01 14:14:41      NaN      NaN      NaN      NaN      NaN      NaN   \n",
       "2019-01-02 14:17:14      NaN      NaN      NaN      NaN      NaN    214.8   \n",
       "2019-01-02 16:34:44      NaN      NaN      NaN    323.8      NaN      NaN   \n",
       "...                      ...      ...      ...      ...      ...      ...   \n",
       "2019-10-12 11:02:22      NaN    249.0      NaN      NaN      NaN      NaN   \n",
       "2019-10-13 16:35:29      NaN      NaN      NaN      NaN      NaN      NaN   \n",
       "2019-10-13 19:09:28      NaN      NaN      NaN      NaN      NaN      NaN   \n",
       "2019-10-14 15:17:29      NaN      NaN      NaN      NaN      NaN      NaN   \n",
       "2019-10-14 16:06:23      NaN    108.8      NaN      NaN      NaN      NaN   \n",
       "\n",
       "顾客                   BID2391  BID2426  BID2603  BID2652  BID2916  BID3313  \\\n",
       "购买时间                                                                        \n",
       "2019-01-01 09:28:37      NaN      NaN      NaN      NaN    259.3      NaN   \n",
       "2019-01-01 11:18:38      NaN      NaN      NaN      NaN      NaN      NaN   \n",
       "2019-01-01 14:14:41      NaN      NaN      NaN      NaN      NaN      NaN   \n",
       "2019-01-02 14:17:14      NaN      NaN      NaN      NaN      NaN      NaN   \n",
       "2019-01-02 16:34:44      NaN      NaN      NaN      NaN      NaN      NaN   \n",
       "...                      ...      ...      ...      ...      ...      ...   \n",
       "2019-10-12 11:02:22      NaN      NaN      NaN      NaN      NaN      NaN   \n",
       "2019-10-13 16:35:29      NaN      NaN      NaN      NaN      NaN      NaN   \n",
       "2019-10-13 19:09:28      NaN      NaN      NaN      NaN      NaN      NaN   \n",
       "2019-10-14 15:17:29      NaN      NaN      NaN    427.2      NaN      NaN   \n",
       "2019-10-14 16:06:23      NaN      NaN      NaN      NaN      NaN      NaN   \n",
       "\n",
       "顾客                   BID3413  BID3425  \n",
       "购买时间                                   \n",
       "2019-01-01 09:28:37      NaN      NaN  \n",
       "2019-01-01 11:18:38      NaN      NaN  \n",
       "2019-01-01 14:14:41      NaN      NaN  \n",
       "2019-01-02 14:17:14      NaN      NaN  \n",
       "2019-01-02 16:34:44      NaN      NaN  \n",
       "...                      ...      ...  \n",
       "2019-10-12 11:02:22      NaN      NaN  \n",
       "2019-10-13 16:35:29    148.7      NaN  \n",
       "2019-10-13 19:09:28      NaN      NaN  \n",
       "2019-10-14 15:17:29      NaN      NaN  \n",
       "2019-10-14 16:06:23      NaN      NaN  \n",
       "\n",
       "[301 rows x 20 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_Pivot_1 = pd.pivot_table(df_总额前20名顾客药品销售清单, \n",
    "                            values='总价',aggfunc=\"sum\",index='购买时间',columns='顾客')\n",
    "df_Pivot_1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### （2）使用pivot_table函数制作透视表，分析每名顾客在每家药店购买的药品的总金额和总数量。\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"10\" halign=\"left\">总价</th>\n",
       "      <th>...</th>\n",
       "      <th colspan=\"10\" halign=\"left\">数量</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>顾客</th>\n",
       "      <th>BID0462</th>\n",
       "      <th>BID0596</th>\n",
       "      <th>BID0666</th>\n",
       "      <th>BID0741</th>\n",
       "      <th>BID0788</th>\n",
       "      <th>BID1006</th>\n",
       "      <th>BID1131</th>\n",
       "      <th>BID1178</th>\n",
       "      <th>BID1437</th>\n",
       "      <th>BID1994</th>\n",
       "      <th>...</th>\n",
       "      <th>BID2118</th>\n",
       "      <th>BID2350</th>\n",
       "      <th>BID2391</th>\n",
       "      <th>BID2426</th>\n",
       "      <th>BID2603</th>\n",
       "      <th>BID2652</th>\n",
       "      <th>BID2916</th>\n",
       "      <th>BID3313</th>\n",
       "      <th>BID3413</th>\n",
       "      <th>BID3425</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>药店编码</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>SID01</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>357.6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>815.3</td>\n",
       "      <td>901.9</td>\n",
       "      <td>806.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SID02</th>\n",
       "      <td>NaN</td>\n",
       "      <td>373.9</td>\n",
       "      <td>123.6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>108.8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>74.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SID03</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>256.3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>227.9</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>205.8</td>\n",
       "      <td>558.6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>18.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9.0</td>\n",
       "      <td>93.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SID04</th>\n",
       "      <td>4739.2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>426.3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>669.2</td>\n",
       "      <td>3023.4</td>\n",
       "      <td>60.6</td>\n",
       "      <td>823.1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>212.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SID05</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5437.9</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>25.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>13.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SID06</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>901.7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>160.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SID07</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1792.3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>773.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>22.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SID08</th>\n",
       "      <td>1056.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>512.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>11.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SID09</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>527.8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>197.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>37.6</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SID10</th>\n",
       "      <td>NaN</td>\n",
       "      <td>547.9</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>91.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>123.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>11.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SID11</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4868.8</td>\n",
       "      <td>444.6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>403.6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SID12</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1614.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SID13</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>725.4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>281.8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>482.4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>92.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SID14</th>\n",
       "      <td>NaN</td>\n",
       "      <td>163.5</td>\n",
       "      <td>318.1</td>\n",
       "      <td>524.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3794.6</td>\n",
       "      <td>448.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>33.0</td>\n",
       "      <td>105.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SID15</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>273.8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>274.3</td>\n",
       "      <td>738.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SID16</th>\n",
       "      <td>NaN</td>\n",
       "      <td>7219.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4166.8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>263.9</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>27.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SID17</th>\n",
       "      <td>93.1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>786.2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>824.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1468.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SID18</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>201.3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>129.8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>148.0</td>\n",
       "      <td>249.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>10.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>92.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SID19</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>316.3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>980.7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>13.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>78.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SID20</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>228.2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>28.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SID21</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>726.6</td>\n",
       "      <td>259.0</td>\n",
       "      <td>302.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>21.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SID22</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>271.9</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SID23</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>562.4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>288.2</td>\n",
       "      <td>383.1</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>23 rows × 40 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           总价                                                                  \\\n",
       "顾客    BID0462 BID0596 BID0666 BID0741 BID0788 BID1006 BID1131 BID1178 BID1437   \n",
       "药店编码                                                                            \n",
       "SID01     NaN     NaN     NaN   357.6     NaN     NaN   815.3   901.9   806.0   \n",
       "SID02     NaN   373.9   123.6     NaN     NaN     NaN     NaN   108.8     NaN   \n",
       "SID03     NaN     NaN   256.3     NaN   227.9     NaN     NaN   205.8   558.6   \n",
       "SID04  4739.2     NaN   426.3     NaN   669.2  3023.4    60.6   823.1     NaN   \n",
       "SID05     NaN     NaN     NaN     NaN     NaN     NaN     NaN     NaN     NaN   \n",
       "SID06     NaN     NaN     NaN     NaN     NaN   901.7     NaN     NaN     NaN   \n",
       "SID07     NaN     NaN  1792.3     NaN     NaN     NaN   773.0     NaN     NaN   \n",
       "SID08  1056.0     NaN     NaN     NaN     NaN     NaN   512.5     NaN     NaN   \n",
       "SID09     NaN     NaN     NaN   527.8     NaN     NaN     NaN   197.0     NaN   \n",
       "SID10     NaN   547.9     NaN     NaN     NaN    91.5     NaN   123.0     NaN   \n",
       "SID11     NaN     NaN     NaN  4868.8   444.6     NaN     NaN     NaN   403.6   \n",
       "SID12     NaN     NaN     NaN     NaN     NaN     NaN     NaN  1614.0     NaN   \n",
       "SID13     NaN     NaN   725.4     NaN     NaN   281.8     NaN     NaN   482.4   \n",
       "SID14     NaN   163.5   318.1   524.5     NaN     NaN  3794.6   448.0     NaN   \n",
       "SID15     NaN     NaN   273.8     NaN     NaN     NaN     NaN   274.3   738.0   \n",
       "SID16     NaN  7219.5     NaN     NaN  4166.8     NaN     NaN   263.9     NaN   \n",
       "SID17    93.1     NaN   786.2     NaN     NaN   824.5     NaN     NaN  1468.5   \n",
       "SID18     NaN     NaN   201.3     NaN   129.8     NaN   148.0   249.0     NaN   \n",
       "SID19     NaN     NaN   316.3     NaN     NaN     NaN     NaN     NaN   980.7   \n",
       "SID20     NaN     NaN     NaN     NaN     NaN     NaN     NaN   228.2     NaN   \n",
       "SID21     NaN     NaN     NaN     NaN     NaN   726.6   259.0   302.0     NaN   \n",
       "SID22     NaN     NaN     NaN     NaN   271.9     NaN     NaN     NaN     NaN   \n",
       "SID23     NaN     NaN   562.4     NaN     NaN     NaN     NaN     NaN   288.2   \n",
       "\n",
       "               ...      数量                                                  \\\n",
       "顾客    BID1994  ... BID2118 BID2350 BID2391 BID2426 BID2603 BID2652 BID2916   \n",
       "药店编码           ...                                                           \n",
       "SID01     NaN  ...     NaN     NaN     NaN     NaN     9.0     NaN     NaN   \n",
       "SID02     NaN  ...     NaN     NaN     NaN     NaN    10.0     NaN     NaN   \n",
       "SID03     NaN  ...     NaN     NaN     NaN    18.0     NaN    15.0     NaN   \n",
       "SID04     NaN  ...     NaN   212.0    21.0    11.0     NaN     NaN     NaN   \n",
       "SID05  5437.9  ...     NaN     NaN     NaN     NaN     NaN    25.0     NaN   \n",
       "SID06     NaN  ...     NaN     NaN   160.0     NaN     NaN     NaN     NaN   \n",
       "SID07     NaN  ...     NaN     NaN     NaN     NaN     NaN     8.0     NaN   \n",
       "SID08     NaN  ...    11.0     NaN     NaN     4.0     NaN     1.0     NaN   \n",
       "SID09    37.6  ...     NaN     NaN     NaN     NaN     NaN    20.0     NaN   \n",
       "SID10     NaN  ...     NaN     NaN     NaN     NaN    14.0     NaN     NaN   \n",
       "SID11     NaN  ...     NaN     NaN     NaN     NaN     NaN     NaN     NaN   \n",
       "SID12     NaN  ...     NaN     NaN     NaN     NaN     NaN     NaN     NaN   \n",
       "SID13     NaN  ...    92.0     NaN     NaN     5.0     NaN     4.0     NaN   \n",
       "SID14     NaN  ...     NaN    14.0     NaN    33.0   105.0     1.0     NaN   \n",
       "SID15     NaN  ...     NaN     NaN     NaN     NaN     NaN     NaN     NaN   \n",
       "SID16     NaN  ...     NaN     NaN    27.0     NaN     NaN    20.0     NaN   \n",
       "SID17     NaN  ...     NaN     NaN     NaN     NaN     NaN     NaN     NaN   \n",
       "SID18     NaN  ...    10.0     NaN     NaN     NaN     NaN     NaN    92.0   \n",
       "SID19     NaN  ...     NaN     NaN     NaN    13.0     NaN     NaN     NaN   \n",
       "SID20     NaN  ...     NaN     NaN     NaN     NaN     NaN    28.0    12.0   \n",
       "SID21     NaN  ...     NaN     NaN     NaN    21.0     NaN    10.0     NaN   \n",
       "SID22     NaN  ...     NaN     NaN     NaN     6.0     NaN     6.0     NaN   \n",
       "SID23   383.1  ...     NaN     NaN     NaN     9.0     NaN     NaN     NaN   \n",
       "\n",
       "                               \n",
       "顾客    BID3313 BID3413 BID3425  \n",
       "药店编码                           \n",
       "SID01     NaN     NaN     NaN  \n",
       "SID02    74.0     NaN     NaN  \n",
       "SID03     9.0    93.0     NaN  \n",
       "SID04     NaN     NaN     NaN  \n",
       "SID05    13.0     NaN     NaN  \n",
       "SID06     NaN     NaN     NaN  \n",
       "SID07     6.0     4.0    22.0  \n",
       "SID08     NaN    10.0     NaN  \n",
       "SID09     2.0     NaN     NaN  \n",
       "SID10     2.0     4.0    11.0  \n",
       "SID11     NaN     NaN     NaN  \n",
       "SID12     3.0     NaN     NaN  \n",
       "SID13     NaN     3.0    15.0  \n",
       "SID14     NaN     NaN     NaN  \n",
       "SID15     2.0     NaN     NaN  \n",
       "SID16    11.0     NaN     NaN  \n",
       "SID17     NaN     NaN     NaN  \n",
       "SID18     NaN     NaN     NaN  \n",
       "SID19     3.0     NaN    78.0  \n",
       "SID20     NaN     NaN     NaN  \n",
       "SID21     4.0     5.0     NaN  \n",
       "SID22     3.0     NaN     NaN  \n",
       "SID23     NaN     NaN     NaN  \n",
       "\n",
       "[23 rows x 40 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_Pivot_2 = pd.pivot_table(df_总额前20名顾客药品销售清单, values=['总价','数量'],\n",
    "                            aggfunc=\"sum\",index='药店编码',columns='顾客')\n",
    "df_Pivot_2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3．获取信息购买总额最高的前20名购药者姓名编码。分析他们中有多少人的信息熵排名在前20名之中。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 计算所有购药者的信息墒并降序显示"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0        BID1062\n",
       "1        BID1062\n",
       "2        BID3241\n",
       "3        BID3241\n",
       "4        BID1899\n",
       "          ...   \n",
       "99728    BID3265\n",
       "99729    BID0704\n",
       "99730    BID0222\n",
       "99731    BID0222\n",
       "99732    BID0222\n",
       "Name: 顾客, Length: 99733, dtype: object"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "se = df_药品销售清单['顾客']\n",
    "se.unique()\n",
    "se"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "BID2104    3.583498\n",
       "BID2559    3.572423\n",
       "BID1786    3.397189\n",
       "BID1785    3.392656\n",
       "BID2271    3.375921\n",
       "             ...   \n",
       "BID2210    0.000000\n",
       "BID0168    0.000000\n",
       "BID0137    0.000000\n",
       "BID2181    0.000000\n",
       "BID2890    0.000000\n",
       "Length: 3721, dtype: float64"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def cal_IE(x):\n",
    "    if len(x) == 1:\n",
    "        return 0.0\n",
    "    else:\n",
    "        p = x/(x.sum())\n",
    "        # print(p)\n",
    "        # print(\"Hello world\")\n",
    "        # print(np.sum(x)[0])\n",
    "        # - (p*np.log2(p)).sum()\n",
    "        # print((-(p*np.log2(p)).sum()).values[0])\n",
    "        return (-(p*np.log2(p)).sum()).values[0]\n",
    "\n",
    "ar_顾客 = df_药品销售清单['顾客'].unique()#总共有3721名顾客\n",
    "sr_IE = pd.Series(0.0,index =ar_顾客) #信息熵的空系列,以购药者ID为行名\n",
    "for 某顾客 in ar_顾客: #循环3721次，计算每一名顾客的信息熵\n",
    "    sr_sel_某顾客= df_药品销售清单['顾客'] == 某顾客 #筛选某顾客条件\n",
    "    df_某顾客 = df_药品销售清单.loc[sr_sel_某顾客,['药店编码','总价']]\n",
    "    #筛选某个购药者的全部购药记录\n",
    "    dfg_药店=df_某顾客.groupby(by='药店编码')    \n",
    "    df_某顾客在某家药店购药总额= dfg_药店.sum() #获取用于求信息熵的系列\n",
    "    某顾客_IE = cal_IE(df_某顾客在某家药店购药总额)\n",
    "    sr_IE[某顾客] = 某顾客_IE\n",
    "\n",
    "sr_IE.sort_values(ascending = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* 解释计算所有顾客信息熵程序。以一名顾客为例"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "某顾客= 'BID2104'\n",
    "sr_sel_某顾客= df_药品销售清单['顾客'] ==某顾客 #筛选某顾客条件\n",
    "df_某顾客 = df_药品销售清单.loc[sr_sel_某顾客,['药店编码','总价']]\n",
    "df_某顾客.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "dfg_药店=df_某顾客.groupby(by='药店编码')    \n",
    "df_某顾客在某家药店购药总额= dfg_药店.sum() #获取用于求信息熵的系列\n",
    "某顾客_IE = cal_IE(df_某顾客在某家药店购药总额)\n",
    "sr_IE[某顾客] = 某顾客_IE\n",
    "print('*',end =\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "某顾客_IE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "sr_IE[某顾客]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 信息熵最高的前20名顾客"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "sr_信息熵前20名顾客=sr_IE.sort_values(ascending = False).head(20)\n",
    "sr_信息熵前20名顾客.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sr_总价前20名顾客.index"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 购药总价前20顾客中信息墒处于前20的名单（重点审计对象）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "[  bid for  bid in sr_总价前20名顾客.index  if bid in sr_信息熵前20名顾客.index   ]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 购药总价前100顾客中信息墒处于前20的名单（重点审计对象）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "idx_购药总额前100名顾客名单=df_顾客购药总额.head(100).index\n",
    "[ bid for  bid in idx_购药总额前100名顾客名单 if bid in sr_信息熵前20名顾客.index]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def calc_IE(x):\n",
    "    # x是一个组合型数据(列表、数组、系列、数据框皆可)，x的每个元素表示在单家药店的总额 \n",
    "        if len(x) == 1:\n",
    "            return 0\n",
    "        else:\n",
    "            p = x/np.sum(x)\n",
    "            return - np.sum(p*np.log2(p))\n",
    "\n",
    "ar_顾客 = df_药品销售清单['顾客'].unique()#总共有3721名顾客\n",
    "sr_IE = pd.Series(0.0,index =ar_顾客) #信息熵的空系列,以购药者ID为行名\n",
    "for 某顾客 in ar_顾客: #循环3721次，计算每一名顾客的信息熵\n",
    "    sr_sel_某顾客= df_药品销售清单['顾客'] == 某顾客 #筛选某顾客条件\n",
    "    df_某顾客 = df_药品销售清单.loc[sr_sel_某顾客,['药店编码','总价']]\n",
    "    #筛选某个购药者的全部购药记录\n",
    "    dfg_药店=df_某顾客.groupby(by='药店编码')    \n",
    "    df_某顾客在某家药店购药总额= dfg_药店.sum() #获取用于求信息熵的系列\n",
    "    某顾客_IE = calc_IE(df_某顾客在某家药店购药总额)\n",
    "    sr_IE[某顾客] = 某顾客_IE\n",
    "    print('*',end =\"\")\n",
    "\n",
    "sr_IE.sort_values(ascending = False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "mining",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
